﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using DiscoveryLogic.Analytics.DataObjects;
using DiscoveryLogic.Analytics.DataObjects.Networks;
using DiscoveryLogic.Common.Utilities;

namespace DiscoveryLogic.Analytics.BusinessLogic.Scenarios
{
    public class OrgNetworkBuilder
    {
        public static Network BuildNetworkViaMeshTerms(
            string networkName, List<string> meshTerms,
            bool isMajorTopic, List<int> pubYrs)
        {
            Network network = new Network(networkName, new List<EntityBase>(), new List<Link>());
            string connStr = ConfigurationManager.ConnectionStrings["Medline"].ConnectionString;
            SqlConnection conn=new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.Text;
                string sql = @"
SELECT DISTINCT
	C.PMID,
    YEAR(CSI.DLPubDate) as PubYear,
	O.ORG_NAME
FROM
	MEDLINE..Citation C
	JOIN MEDLINE..CitationMesh CM
		ON C.PMID = CM.PMID		
	JOIN MEDLINE..MeshDescriptor MD
		ON CM.MeshDescriptorId = MD.MeshDescriptorId
		AND MD.MeshDescriptor in ({0})
        AND CM.MajorTopicYN = {1}
	JOIN MEDLINE..CitationSourceItem CSI
		ON C.PMID = CSI.PMID
		AND YEAR(DLPubDate) in ({2})
	LEFT OUTER JOIN SynapseNew..Link L
		ON L.FromObjectId = C.PMID
			AND L.ToObjectTypeId = 1
			AND L.FromObjectTypeId = 2
			AND L.LinkSourceId = 1
	LEFT OUTER JOIN NIHProject..Project P
		ON P.ProjectId = L.ToObjectId
	LEFT OUTER JOIN NIHProject..Appl A
		ON P.CurrentApplId = A.APPL_ID
	LEFT OUTER JOIN impacii_mirror.irdb.external_orgs_mv O
		ON O.external_org_id = A.external_org_id
WHERE
	O.Org_Name IS NOT NULL
ORDER BY
	O.ORG_NAME";
                string inMesh = string.Empty;
                foreach(string meshTerm in meshTerms)
                {
                    if (inMesh.Length > 0)
                        inMesh += ",";
                    inMesh += "'" + meshTerm + "'";
                }
                string majorTopic = "1";
                if (!isMajorTopic)
                    majorTopic = "0";
                string inYrs = string.Empty;
                if (pubYrs != null && pubYrs.Count > 0)
                {
                    foreach (int pubYr in pubYrs)
                    {
                        if (inYrs.Length > 0)
                            inYrs += ",";
                        inYrs += pubYr.ToString();
                    }
                }
                else
                    inYrs = "2008";

                cmd.CommandText = string.Format(sql, inMesh, majorTopic, inYrs);
                SqlDataReader reader = cmd.ExecuteReader();
                Dictionary<int, int> pmid_PubYr=new Dictionary<int, int>();
                Dictionary<string,List<int>> org_Pmids=new Dictionary<string, List<int>>();
                while(reader.Read())
                {
                    int pmid = int.Parse(reader["PMID"].ToString());
                    string orgName = reader["Org_Name"].ToString();
                    int pubYear = int.Parse(reader["PubYear"].ToString());
                    if (!pmid_PubYr.ContainsKey(pmid))
                    {
                        pmid_PubYr.Add(pmid, pubYear);
                    }
                    
                    if(org_Pmids.ContainsKey(orgName))
                    {
                        List<int> pmids = org_Pmids[orgName];
                        if(!pmids.Contains(pmid))
                            pmids.Add(pmid);
                        org_Pmids[orgName] = pmids;
                    }
                    else
                    {
                        List<int> pmids=new List<int>();
                        pmids.Add(pmid);
                        org_Pmids.Add(orgName, pmids);
                    }
                }
                reader.Close();

                int orgID = 0;
                Dictionary<string,int> orgName_orgID=new Dictionary<string, int>();
                foreach(string orgName in org_Pmids.Keys)
                {
                    orgID++;
                    ExternalOrgDO org=new ExternalOrgDO();
                    org.ExternalOrgID = orgID;
                    org.OrgName = orgName;
                    network.Records.Add(org);
                    orgName_orgID.Add(orgName, orgID);
                }
                List<string> orgNames2 = orgName_orgID.Keys.ToList();
                for(int i=0;i<orgNames2.Count;i++)
                {
                    string orgName1 = orgNames2[i];
                    int orgID1 = orgName_orgID[orgName1];
                    List<int> pmids1=new List<int>();
                    if (org_Pmids.ContainsKey(orgName1))
                        pmids1 = org_Pmids[orgName1];
                    for(int k=i+1;k<orgNames2.Count;k++)
                    {
                        string orgName2 = orgNames2[k];
                        int orgID2 = orgName_orgID[orgName2];
                        List<int> pmids2=new List<int>();
                        if (org_Pmids.ContainsKey(orgName2))
                            pmids2 = org_Pmids[orgName2];
                        List<int> shared = IntUtil.Intersection(pmids1, pmids2);
                        if(shared.Count>0)
                        {
                            Link link=new Link(typeof(ExternalOrgDO), typeof(ExternalOrgDO),
                                orgID1.ToString(), orgID2.ToString(),
                                1.0, shared.Count, LinkType.Citation, false);
                            DateTime fromDate = TimeUtil.DbMinTime;
                            DateTime toDate = TimeUtil.DbMaxTime;
                            foreach(int sharedPmid in shared)
                            {
                                if(pmid_PubYr.ContainsKey(sharedPmid))
                                {
                                    DateTime pubTime=new DateTime(pmid_PubYr[sharedPmid], 1, 1);
                                    if(fromDate==TimeUtil.DbMinTime || pubTime < fromDate)
                                        fromDate = pubTime;
                                    if(toDate==TimeUtil.DbMaxTime || pubTime > toDate)
                                        toDate = pubTime;
                                }
                            }
                            Range<DateTime> timeRange=new Range<DateTime>(fromDate, toDate);
                            link.LinkTimeSpan = timeRange;
                            network.Links.Add(link);
                        }
                    }
                }
            }
            catch(Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
            return network;
        }

        public static Network BuildNetworkViaPMIDs(
            string networkName, List<int> inPMIDs)
        {
            Network network = new Network(networkName, new List<EntityBase>(), new List<Link>());
            string connStr = ConfigurationManager.ConnectionStrings["Medline"].ConnectionString;
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.Text;
                string sql = @"
select 
distinct 
  o.Org_Name, 
  c.FromObjectID as PMID, 
  YEAR(CSI.DLPubDate) as PubYear 
from 
NihProject.dbo.Project a 
inner join NihProject.dbo.Appl b on a.projectid=b.project_id and a.CurrentApplID=b.Appl_ID
inner join IMPACII_Mirror.IRDB.External_Orgs_MV O on b.EXTERNAL_ORG_ID=o.EXTERNAL_ORG_ID 
inner join SynapseNew..Link c ON a.ProjectID=c.ToObjectID 
JOIN MEDLINE..CitationSourceItem CSI ON c.FromObjectID = CSI.PMID
where 
  c.FromObjectTypeID=2 and c.ToObjectTypeID=1 and 
  c.LinkSourceID in (1,5) and c.FromObjectID in (
    select 
    a.PMID
    from Medline.dbo.Citation a 
    left join Medline.dbo.JournalIssue b on a.JournalIssueID=b.JournalIssueID 
    left join Medline.dbo.Journal c on c.JournalID=b.JournalID
    where a.PMID in (
    select distinct FromObjectID 
    from SynapseNew..Link 
    where 
     FromObjectTypeID=2 and ToObjectTypeID=1 and 
     LinkSourceID in (1,5) and ToObjectID in ({0})
    ))
ORDER BY
	o.ORG_NAME";
                string pmidFilter = string.Empty;
                foreach (int pmid in inPMIDs)
                {
                    if (pmidFilter.Length > 0)
                        pmidFilter += ",";
                    pmidFilter += pmid.ToString();
                }

                cmd.CommandText = string.Format(sql, pmidFilter);
                SqlDataReader reader = cmd.ExecuteReader();
                Dictionary<int, int> pmid_PubYr = new Dictionary<int, int>();
                Dictionary<string, List<int>> org_Pmids = new Dictionary<string, List<int>>();
                while (reader.Read())
                {
                    int pmid = int.Parse(reader["PMID"].ToString());
                    string orgName = reader["Org_Name"].ToString();
                    int pubYear = int.Parse(reader["PubYear"].ToString());
                    if (!pmid_PubYr.ContainsKey(pmid))
                    {
                        pmid_PubYr.Add(pmid, pubYear);
                    }

                    if (org_Pmids.ContainsKey(orgName))
                    {
                        List<int> pmids = org_Pmids[orgName];
                        if (!pmids.Contains(pmid))
                            pmids.Add(pmid);
                        org_Pmids[orgName] = pmids;
                    }
                    else
                    {
                        List<int> pmids = new List<int>();
                        pmids.Add(pmid);
                        org_Pmids.Add(orgName, pmids);
                    }
                }
                reader.Close();

                int orgID = 0;
                Dictionary<string, int> orgName_orgID = new Dictionary<string, int>();
                foreach (string orgName in org_Pmids.Keys)
                {
                    orgID++;
                    ExternalOrgDO org = new ExternalOrgDO();
                    org.ExternalOrgID = orgID;
                    org.OrgName = orgName;
                    network.Records.Add(org);
                    orgName_orgID.Add(orgName, orgID);
                }
                List<string> orgNames2 = orgName_orgID.Keys.ToList();
                for (int i = 0; i < orgNames2.Count; i++)
                {
                    string orgName1 = orgNames2[i];
                    int orgID1 = orgName_orgID[orgName1];
                    List<int> pmids1 = new List<int>();
                    if (org_Pmids.ContainsKey(orgName1))
                        pmids1 = org_Pmids[orgName1];
                    for (int k = i + 1; k < orgNames2.Count; k++)
                    {
                        string orgName2 = orgNames2[k];
                        int orgID2 = orgName_orgID[orgName2];
                        List<int> pmids2 = new List<int>();
                        if (org_Pmids.ContainsKey(orgName2))
                            pmids2 = org_Pmids[orgName2];
                        List<int> shared = IntUtil.Intersection(pmids1, pmids2);
                        if (shared.Count > 0)
                        {
                            Link link = new Link(typeof(ExternalOrgDO), typeof(ExternalOrgDO),
                                orgID1.ToString(), orgID2.ToString(),
                                1.0, shared.Count, LinkType.Citation, false);
                            DateTime fromDate = TimeUtil.DbMinTime;
                            DateTime toDate = TimeUtil.DbMaxTime;
                            foreach (int sharedPmid in shared)
                            {
                                if (pmid_PubYr.ContainsKey(sharedPmid))
                                {
                                    DateTime pubTime = new DateTime(pmid_PubYr[sharedPmid], 1, 1);
                                    if (fromDate == TimeUtil.DbMinTime || pubTime < fromDate)
                                        fromDate = pubTime;
                                    if (toDate == TimeUtil.DbMaxTime || pubTime > toDate)
                                        toDate = pubTime;
                                }
                            }
                            Range<DateTime> timeRange = new Range<DateTime>(fromDate, toDate);
                            link.LinkTimeSpan = timeRange;
                            network.Links.Add(link);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
            return network;
        }
    }
}
